import time
from datetime import datetime
from enum import Enum

import mysql.connector


class DatabaseCommandState(Enum):
    SET = 0
    EXECUTING = 1
    SUCCESS = 2
    FAILED = 3
    GET = 4


class MyDatabase(object):
    def __init__(self, user: str, password: str, host: tuple, data_base_name: str):
        """
        构造数据库操作对象
        :param user: 用户名
        :param password: 密码
        :param host: （主机ip，端口）
        :param data_base_name: 数据库名
        """
        self.cnx = mysql.connector.connect(user=user, password=password, host=host[0], port=host[1],
                                           database=data_base_name, auth_plugin='mysql_native_password')
        self.cursor = self.cnx.cursor()

    def __enter__(self):
        """
        for with
        """
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        for with
        """
        if exc_type is not None:
            print("==MyDatabase==")
            print(exc_type)
            print(exc_val)
            print(exc_tb)
            print("=============")
        self.cnx.commit()
        self.cursor.close()
        self.cnx.close()
        return False


class Database(object):
    """
    服务器所需的数据库操作
    """

    def __init__(self, user: str, password: str, host: tuple, data_base_name: str):
        """
        构造数据库操作对象
        :param user: 用户名
        :param password: 密码
        :param host: （主机ip，端口）
        :param data_base_name: 数据库名
        """
        self.connect_param = (user, password, host, data_base_name)

    def insert_relay_history(self, data_relay: tuple = None, do_idx_state: tuple = None):
        """
        添加一条读取继电器模块状态的记录，data_relay或do_idx二选一的
        给定data_relay代表插入一条按照data_relay指定的新记录
        给定do_idx代表读取最后一条记录并修改其中的DO状态，然会作为新记录插入到数据表中
        :param data_relay: ModbusReadCoilValueEnum组成的元组，即（DO1状态，DO2状态，……，DO16状态）
        :param do_idx_state: 元组：（从0开始的DO索引，1代表ON/0代表OFF）
        :return: 无
        """
        if data_relay is None and do_idx_state is None:
            raise AssertionError("The two parameters sata_relay and do_idx_state is None")
        if data_relay is not None:
            with MyDatabase(*self.connect_param) as cursor:
                query = r"INSERT INTO relay_history VALUES (%s, %s, %s" + ", %s" * 16 + ")"
                parameters_list = [None, 1, datetime.now()]
                parameters_list.extend([i.value for i in data_relay])
                cursor.execute(query, tuple(parameters_list))
        else:
            with MyDatabase(*self.connect_param) as cursor:
                query = r"SELECT * FROM relay_history ORDER BY id DESC LIMIT 1"
                cursor.execute(query)
                one_line = list(cursor.fetchall()[0][3:])
                one_line[do_idx_state[0]] = do_idx_state[1]
                new_one_line = [None, 1, datetime.now()]
                new_one_line.extend(one_line)

            with MyDatabase(*self.connect_param) as cursor:
                query = r"INSERT INTO relay_history VALUES (%s, %s, %s" + ", %s" * 16 + ")"
                cursor.execute(query, tuple(new_one_line))

    def insert_rtu_history(self, rtu_idx: int, data_do_di: tuple = None, do_idx_state: tuple = None):
        """
        添加一条读取rtu模块状态的记录，data_do_di或ddo_idx_state二选一的
        给定data_do_di代表插入一条按照data_do_di指定的新记录，只考虑data_do_di[0][0~1]和data_do_di[1][0~1]
        给定do_idx_state代表读取最后一条记录并修改其中的DO状态，然会作为新记录插入到数据表中
        :param rtu_idx: 从0开始的rtu索引
        :param data_do_di: ModbusReadCoilValueEnum组成的元组再组成一个元组，即（（DO1状态，DO2状态，……），（DI1电平，DO2电平，……））
                           只考虑DO1、DO2、DI1、DI2
        :param do_idx_state: 元组：（从0开始的DO索引，1代表ON/0代表OFF）
        :return: 无
        """
        if data_do_di is None and do_idx_state is None:
            raise AssertionError("The two parameters data_do_di and do_idx_state is None")
        if data_do_di is not None:
            data_do = data_do_di[0]
            data_di = data_do_di[1]
            with MyDatabase(*self.connect_param) as cursor:
                query = r"INSERT INTO rtu_history VALUES (%s, %s, %s" + ", %s" * 4 + ")"
                parameters_list = [None, rtu_idx + 1, datetime.now(), data_di[0].value, data_di[1].value,
                                   data_do[0].value,
                                   data_do[1].value]
                cursor.execute(query, tuple(parameters_list))

        else:
            with MyDatabase(*self.connect_param) as cursor:
                query = f"SELECT * FROM rtu_history WHERE rtu_id={rtu_idx + 1} ORDER BY id DESC LIMIT 1"
                cursor.execute(query)
                one_line = list(cursor.fetchall()[0][3:])

            one_line[do_idx_state[0] + 2] = do_idx_state[1]
            new_one_line = [None, rtu_idx + 1, datetime.now()]
            new_one_line.extend(one_line)

            with MyDatabase(*self.connect_param) as cursor:
                query = r"INSERT INTO rtu_history VALUES (%s, %s, %s" + ", %s" * 4 + ")"
                cursor.execute(query, tuple(new_one_line))

    def insert_rasp_history(self, rasp_idx: int, data_regs: tuple):
        if data_regs is None:
            raise AssertionError("The parameter data_regs is None")
        with MyDatabase(*self.connect_param) as cursor:
            query = r"INSERT INTO rasp_history VALUES (%s, %s, %s" + ", %s" * 4 + ")"
            parameters_list = [None, rasp_idx + 1, datetime.now(), data_regs[0], data_regs[1],
                               data_regs[2], data_regs[3]]
            cursor.execute(query, tuple(parameters_list))

    def query_relay_command(self) -> tuple:
        """
        查询relay_command中对继电器的指令，若不需操作，则返回格式三
        若需控制继电器，则将DatabaseCommandState.EXECUTING状态写回并返回格式一
        若需读继电器状态，则将DatabaseCommandState.EXECUTING状态写回并返回格式二
        由于只有一台继电器设备，所以格式一和格式二的设备索引应为0
        :return: 格式一：命令DatabaseCommandState.SET，设备索引（从0开始），DO索引（从0开始），"on"/"off"，数据库记录的id
                 格式二：命令DatabaseCommandState.GET，设备索引（从0开始），None，None，数据库记录的id
                 格式三：None，None，None，None，None
        """
        with MyDatabase(*self.connect_param) as cursor:
            query = f"SELECT * FROM relay_command WHERE status={DatabaseCommandState.SET.value} OR status={DatabaseCommandState.GET.value} ORDER BY id LIMIT 1"
            cursor.execute(query)
            one_line = cursor.fetchone()

        if one_line is None:
            return None, None, None, None, None

        if one_line[-1] in (DatabaseCommandState.SET.value, DatabaseCommandState.GET.value):
            with MyDatabase(*self.connect_param) as cursor:
                query = f"UPDATE relay_command SET status={DatabaseCommandState.EXECUTING.value} WHERE id={one_line[0]}"
                cursor.execute(query)

            if one_line[-1] == DatabaseCommandState.SET.value:
                to_set = one_line[3:19]
                if 1 in to_set:
                    return DatabaseCommandState.SET, one_line[1] - 1, \
                           to_set.index(1), "on", one_line[0]
                elif 0 in to_set:
                    return DatabaseCommandState.SET, one_line[1] - 1, \
                           to_set.index(0), "off", one_line[0]
            else:
                return DatabaseCommandState.GET, one_line[1] - 1, \
                       None, None, one_line[0]

        else:
            return None, None, None, None, None

    def query_rtu_command(self) -> tuple:
        """
        查询rtu_command中对rtu的指令，若不需操作，则返回格式三
        若需控制DO，则将DatabaseCommandState.EXECUTING状态写回并返回格式一
        若需读DO和DI状态，则将DatabaseCommandState.EXECUTING状态写回并返回格式二
        :return: 格式一：命令DatabaseCommandState.SET，设备索引（从0开始），DO索引（从0开始），"on"/"off"，数据库记录的id
                 格式二：命令DatabaseCommandState.GET，设备索引（从0开始），None，None，数据库记录的id
                 格式三：None，None，None，None，None
        """
        with MyDatabase(*self.connect_param) as cursor:
            query = f"SELECT * FROM rtu_command WHERE status={DatabaseCommandState.SET.value} OR status={DatabaseCommandState.GET.value} ORDER BY id LIMIT 1"
            cursor.execute(query)
            one_line = cursor.fetchone()

        if one_line is None:
            return None, None, None, None, None

        if one_line[-1] in (DatabaseCommandState.SET.value, DatabaseCommandState.GET.value):
            with MyDatabase(*self.connect_param) as cursor:
                query = f"UPDATE rtu_command SET status={DatabaseCommandState.EXECUTING.value} WHERE id={one_line[0]}"
                cursor.execute(query)

            if one_line[-1] == DatabaseCommandState.SET.value:
                to_set = one_line[3:5]
                if 1 in to_set:
                    return DatabaseCommandState.SET, one_line[1] - 1, \
                           to_set.index(1), "on", one_line[0]
                elif 0 in to_set:
                    return DatabaseCommandState.SET, one_line[1] - 1, \
                           to_set.index(0), "off", one_line[0]
            else:
                return DatabaseCommandState.GET, one_line[1] - 1, \
                       None, None, one_line[0]

        else:
            return None, None, None, None, None

    def query_rasp_command(self) -> tuple:
        """
        查询rasp_command中对rasp的指令，若不需操作，则返回格式二
        若需读状态，则将DatabaseCommandState.EXECUTING状态写回并返回格式一
        :return: 格式一：命令DatabaseCommandState.GET，设备索引（从0开始），None，None，数据库记录的id
                 格式二：None，None，None，None，None
        """
        with MyDatabase(*self.connect_param) as cursor:
            query = f"SELECT * FROM rasp_command WHERE status={DatabaseCommandState.GET.value} ORDER BY id LIMIT 1"
            cursor.execute(query)
            one_line = cursor.fetchone()

        if one_line is None:
            return None, None, None, None, None

        if one_line[-1] == DatabaseCommandState.GET.value:
            with MyDatabase(*self.connect_param) as cursor:
                query = f"UPDATE rasp_command SET status={DatabaseCommandState.EXECUTING.value} WHERE id={one_line[0]}"
                cursor.execute(query)


            return DatabaseCommandState.GET, one_line[1] - 1, None, None, one_line[0]

        else:
            return None, None, None, None, None

    def update_relay_command(self, id: int, state: DatabaseCommandState):
        """
        设置继电器模块指定id的命令的状态
        :param id: relay_command表中段id值
        :param state: 要设置的状态
        :return: 无
        """
        with MyDatabase(*self.connect_param) as cursor:
            query = f"UPDATE relay_command SET status={state.value} WHERE id={id}"
            cursor.execute(query)

    def update_rtu_command(self, id: int, state: DatabaseCommandState):
        """
        设置rtu模块指定id的条命令的状态
        :param id: relay_command表中段id值
        :param state: 要设置的状态
        :return: 无
        """
        with MyDatabase(*self.connect_param) as cursor:
            query = f"UPDATE rtu_command SET status={state.value} WHERE id={id}"
            cursor.execute(query)

    def update_rasp_command(self, id: int, state: DatabaseCommandState):
        """
        设置树莓派模块指定id的条命令的状态
        :param id: rasp_command表中段id值
        :param state: 要设置的状态，只能是SET
        :return: 无
        """
        if state == DatabaseCommandState.SET:
            raise AssertionError("rasp device cannot be set")
        with MyDatabase(*self.connect_param) as cursor:
            query = f"UPDATE rasp_command SET status={state.value} WHERE id={id}"
            cursor.execute(query)

    def update_connect_param(self, user: str, password: str, host: tuple, data_base_name: str):
        """
        构造数据库操作对象
        :param user: 用户名
        :param password: 密码
        :param host: （主机ip，端口）
        :param data_base_name: 数据库名
        """
        self.connect_param = (user, password, host, data_base_name)


if __name__ == "__main__":
    db = Database("jsy_server", "Yangfan886@miaow", ("miaowfun.mysql.rds.aliyuncs.com", 10002), "hehai_jieshuiyuan")
    while True:
        time.sleep(2)
        print(db.query_relay_command())
        print(db.query_rtu_command())
